<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Bank Ledger Periodic Report</title>
</head>
<body>

<h2 align="center">Bank Ledger Periodic Report</h2>
<form name="bankledgerreport" method="get" >

<table align="center" border=5 width=500>
<% 
    String selectedbname = (String) request.getAttribute("bankname");
	String dte=(String) request.getAttribute("frmdte");
	String dte1=(String) request.getAttribute("todte");
	
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		Statement st = null;
		ResultSet rs = null;   
		String d="";
		double total=0.0;
		double ar14=0.0, ar24=0.0, ap14=0.0, ap24=0.0,am=0.0,bank=0.0;
   		%>
   		<%
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT  bankname, Date, sum(amount11),sum(amount22) FROM ( (SELECT bname AS bankname, date as Date, ramount AS amount11, ramount2 AS amount22 FROM ReceiptsVoucher2) UNION ALL (SELECT bname AS bankname, date as Date, -(pyamount) AS amount11, -(pyamount2) AS amount22 FROM PaymentVoucher2) ) p where bankname='"+selectedbname+"' and Date <'"+dte+"' ");
		   
		   while(rs.next())
		   {%>
		   
		   <%
   		String b=rs.getString(1);
    		 d=rs.getString(2);
    		double am1=rs.getDouble(3);
    		 double am2=rs.getDouble(4);
    		 am=am1+am2;
    		%>
    		
    		<%
		   }
		    
   }
   catch(Exception e)
   {}%>
   		
   		<tr><td colspan=5>From :<%=dte %><br>  To: <%=dte1 %></tr>
<tr>
		<td colspan=5 align=center> BOA  </td>
</tr>
 
 
 
<tr>
		<th> Date </th>
		<th> Particulars </th>
		<th> Voucher type </th>
 		<th> Voucher Number </th>
 		<th> Amount </th>
</tr>
<tr><td></td><th>Opening Balance</th><td></td><td></td><td><%=am %></td></tr>

  		
   		<%
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,raccname,ramount FROM ReceiptsVoucher2 where bname='"+selectedbname+"' and (date  between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String r11=rs.getString(1); //receipts voucher number	        
		        String r12=rs.getString(2); //date
		        String r13=rs.getString(3); //account name
				double r14=rs.getDouble(4); //amount22
				
				ar14=ar14+r14;
%>
<tr>
			<td> <%=r12 %> </td>
			<td> <%=r13 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r11%>&group=Receipts"> <%=r11 %> </a></td>
			<td> <%=r14 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,raccname2,ramount2 FROM ReceiptsVoucher2 where bname='"+selectedbname+"' and (date  between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String r21=rs.getString(1); //receipts voucher number	        
		        String r22=rs.getString(2); //date
		        String r23=rs.getString(3); //account name
				double r24=rs.getDouble(4); //amount22
				
				ar24=ar24+r24;
%>
<tr>
			<td> <%=r22 %> </td>
			<td> <%=r23 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r21%>&group=Payment"> <%=r21 %> </a></td>
			<td> <%=r24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,pyaccname,-(pyamount) FROM PaymentVoucher2 where bname='"+selectedbname+"' and (date  between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String p11=rs.getString(1); //Payment voucher number	        
		        String p12=rs.getString(2); //date
		        String p13=rs.getString(3); //account name
				double p14=rs.getDouble(4); //amount22
				
				ap14=ap14+p14;
%>
<tr>
			<td> <%=p12 %> </td>
			<td> <%=p13 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p11%>&group=Payment"> <%=p11 %> </a></td>
			<td> <%=p14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,pyaccname2,-(pyamount2) FROM PaymentVoucher2 where bname='"+selectedbname+"' and (date  between '"+dte+"' and '"+dte1+"');");
		   
		   while(rs.next())
		   {
		        String p21=rs.getString(1); //Payment voucher number	        
		        String p22=rs.getString(2); //date
		        String p23=rs.getString(3); //account name
				double p24=rs.getDouble(4); //amount22
				
				ap24=ap24+p24;
%>
<tr>
			<td> <%=p22 %> </td>
			<td> <%=p23 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p21%>&group=Payment"> <%=p21 %></a> </td>
			<td> <%=p24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

	<% total=ar14+ar24+ap14+ap24; %>
<tr>
		<td> </td>
		<td> Total </td>
		<td> </td>
		<td> </td>
		<td> <%=total %></td>
</tr>

</table>

</form>
</body>
</html>